package com.lzj.dao.impl;

import com.lzj.dao.BaseDao;
import com.lzj.dao.UserDao;
import com.lzj.entity.*;

import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * <p>
 *
 * </p>
 *
 * @autor:lzj
 * @date:2021/12/2
 */
public class UserDaoImpl extends BaseDao implements UserDao {
    @Override
    public User queryUser(String name, String pwd) {//查询账号和密码
        User user = null;
        String sql = "select * from user where username=? and password=?";
        Object[] params={name,pwd};
        rs=executeQuery(sql,params);
        try {
            if (rs.next()){
                int id=rs.getInt("id");
                String username=rs.getString("username");
                String password=rs.getString("password");
                String email = rs.getString("email");
                user =new User(id,username,password,email);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeResouce(conn,rs,pstmt);
        }
        return user;
    }

    @Override
    public User queryUserUsername(String name) {
        User user = null;
        String sql = "select * from user where username=?";
        Object[] params={name};
        rs=executeQuery(sql,params);
        try {
            while(rs.next()){
                int id=rs.getInt("id");
                String username=rs.getString("username");
                String password=rs.getString("password");
                String email = rs.getString("email");
                user =new User(id,username,password,email);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeResouce(conn,rs,pstmt);
        }
        return user;
    }

    @Override
    public UserInfomation queryUserInf(int userId) {
        UserInfomation userIfn = null;
        String sql = "select * from userinfomation where userId=?";
        Object[] params={userId};
        rs=executeQuery(sql,params);
        try {
            if (rs.next()){
                int id=rs.getInt("id");
                String username=rs.getString("username");
                String phoneNumber=rs.getString("phoneNumber");
                String address=rs.getString("address");
                int userid = rs.getInt("userId");
                userIfn =new UserInfomation(id,username,phoneNumber,address,userid);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeResouce(conn,rs,pstmt);
        }
        return userIfn;
    }

    @Override
    public Address queryAddress(int userId) {
        Address address = null;
        String sql = "select * from address where userId=?";
        Object[] params={userId};
        rs=executeQuery(sql,params);
        try {
            if (rs.next()){
                int id=rs.getInt("id");
                String recipients=rs.getString("recipients");
                String fullAddress=rs.getString("fullAddress");
                String postcode=rs.getString("postcode");
                String phoneNumber=rs.getString("phoneNumber");
                int userid = rs.getInt("userId");
                address =new Address(id,recipients,fullAddress,postcode,phoneNumber,userid);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeResouce(conn,rs,pstmt);
        }
        return address;
    }

    @Override
    public Sku querySku(int id) {
        Sku sku = null;
        String sql = "select * from sku where id=?";
        Object[] params={id};
        rs=executeQuery(sql,params);
        try {
            if(rs.next()){
                int Id=rs.getInt("id");
                String url=rs.getString("url");//图片url
                String name1=rs.getString("name");//商品名称
                double price=rs.getDouble("price");//商品价格
                String specification=rs.getString("specification");//商品规格
                int count=rs.getInt("count");//商品库存
                sku=new Sku(Id,url,name1,price,specification,count);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeResouce(conn,rs,pstmt);
        }
        return sku;
    }

    @Override
    public List<ShoppCart> queryShoppCart(int userId) {
        List<ShoppCart> list=new ArrayList();
        ShoppCart shoppCart = null;
        String sql = "select * from shoppcart where userId=?";
        Object[] params={userId};
        rs=executeQuery(sql,params);
        DecimalFormat df=new DecimalFormat("0.00");
        try {
            while (rs.next()){
                int skuId=rs.getInt("skuId");
                String url=rs.getString("url");//图片url
                String name=rs.getString("name");//商品名字
                double price=rs.getDouble("price");//商品价格
                String specification=rs.getString("specification");//商品规格
                int count=rs.getInt("count");//购买数量
                double totalPrice=price*count;//单种商品总价
                int userid=rs.getInt("userId");//所属用户id
                shoppCart=new ShoppCart(skuId,url,name,price,specification,count,totalPrice,userid);
                list.add(shoppCart);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeResouce(conn,rs,pstmt);
        }
        return list;
    }

    @Override
    public List<Order> queryOrder(int userId) {
        List<Order> list=new ArrayList();
        Order order = null;
        String sql = "select * from `order` where userId=?";
        Object[] params={userId};
        rs=executeQuery(sql,params);
        try {
            while (rs.next()){
                int id=rs.getInt("id");//订单号
                Timestamp time=rs.getTimestamp("time");//订单时间
                boolean state=rs.getBoolean("state");//订单状态（已支付/未支付）
                int userid=rs.getInt("userId");//所属用户id
                order=new Order(id,time,state,userid);
                list.add(order);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeResouce(conn,rs,pstmt);
        }
        return list;
    }

    @Override
    public List<OrderData> queryOrderData(int userId) {
        List<OrderData> list=new ArrayList();
        OrderData orderData = null;
        String sql = "select * from `orderdata` where userId=?";
        Object[] params={userId};
        rs=executeQuery(sql,params);
        try {
            while (rs.next()){
                int id=rs.getInt("id");
                String url=rs.getString("url");//图片url
                String name=rs.getString("name");//商品名字
                double price=rs.getDouble("price");//商品价格
                String specification=rs.getString("specification");//商品规格
                int count=rs.getInt("count");//购买数量
                double totalPrice=price*count;//单种商品总价
                int orderId=rs.getInt("orderId");
                int userid=rs.getInt("userId");//所属用户id
                orderData=new OrderData(id,url,name,price,specification,count,totalPrice,orderId,userid);
                list.add(orderData);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeResouce(conn,rs,pstmt);
        }
        return list;
    }

    @Override
    public int addUser(User user) {//添加
        String sql = "INSERT INTO USER(username,password,email) VALUES(?,?,?)";
        Object[] params={user.getUsername(),user.getPassword(),user.getEmail()};
        result=executeUpdate(sql,params);
        return result;
    }

    @Override
    public int addUserInf(UserInfomation userInfomation) {
        String sql = "INSERT INTO userinfomation(username,userId) VALUES(?,?)";
        Object[] params={userInfomation.getUsername(),userInfomation.getUserId()};
        result=executeUpdate(sql,params);
        return result;
    }

    @Override
    public int updateUserInf(UserInfomation userInfomation) {
        String sql = "update userinfomation set phoneNumber=?,address=? WHERE userId=?";
        Object[] params = {userInfomation.getPhoneNumber(),userInfomation.getAddress(),userInfomation.getUserId()};
        result = this.executeUpdate(sql, params);
        return result;
    }


    @Override
    public int addAddress(Address address) {
        String sql = "INSERT INTO address(userId) VALUES(?)";
        Object[] params={address.getUserId()};
        result=executeUpdate(sql,params);
        return result;
    }

    @Override
    public int updateAddress(Address address) {
        String sql = "update address set recipients=?,fullAddress=?,postcode=?,phoneNumber=? WHERE userId=?";
        Object[] params = {address.getRecipients(),address.getFullAddress(),address.getPostcode(),address.getPhoneNumber(),address.getUserId()};
        result = this.executeUpdate(sql, params);
        return result;
    }

    @Override
    public int addShoppCart(ShoppCart shoppCart) {
        String sql = "INSERT INTO shoppcart(skuId,url,name,price,specification,count,totalPrice,userId) VALUES(?,?,?,?,?,?,?,?)";
        Object[] params={shoppCart.getSkuId(),shoppCart.getUrl(),shoppCart.getName(),shoppCart.getPrice(),shoppCart.getSpecification(),shoppCart.getCount(),shoppCart.getTotalPrice(),shoppCart.getUserId()};
        result=executeUpdate(sql,params);
        return result;
    }

    @Override
    public int deleteShoppCart(int skuId,int userId) {
        String sql = "delete from shoppcart where skuId=? and userId=?";
        Object[] params={skuId,userId};
        result=executeUpdate(sql,params);
        return result;
    }

    @Override
    public int updateShoppCart(int count,double totalPrice,int skuId,int userId) {
        String sql = "update shoppcart set count=?,totalPrice=? WHERE skuId=? and userId=?";
        Object[] params = {count,totalPrice,skuId,userId};
        result = this.executeUpdate(sql, params);
        return result;
    }

    @Override
    public int addOrder(Order order) {
        String sql = "INSERT INTO `order`(time,state,userId) VALUES(?,?,?)";
        Object[] params={order.getTime(),order.isState(),order.getUserId()};
        result=executeUpdate(sql,params);
        return result;
    }

    @Override
    public int addOrderData(OrderData orderData) {
        String sql = "INSERT INTO orderData(url,name,price,specification,count,totalPrice,orderId,userId) VALUES(?,?,?,?,?,?,?,?)";
        Object[] params={orderData.getUrl(),orderData.getName(),orderData.getPrice(),orderData.getSpecification(),orderData.getCount(),orderData.getTotalPrice(),orderData.getOrderId(),orderData.getUserId()};
        result=executeUpdate(sql,params);
        return result;
    }
}
